<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 

"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Balance Sheet</title>
</head>
<body>
<form>
<center><h2> Balance Sheet </h2> </center>
<table align="center" border=2 width=700>
<tr>
<th colspan=6><font color=red>Balance Sheet</font></th>
</tr>

<tr>
<th ><font color=red>Liabilities</font></th>
<th><font color=red>Rs.</font></th>
<th><font color=red>Rs.</font></th>
<th><font color=red>Assets</font></th>
<th><font color=red>Rs.</font></th>
<th><font color=red>Rs.</font></th>
</tr>

<%
 
		DBConnection dbc=new DBConnection();
		Connection con=dbc.getNewConnection();
		Statement st = null;
		ResultSet rs = null;   
		
		double capital=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Capital'");
		   while(rs.next())
		   {
		        double c1=rs.getDouble(1); // amount
		        String c2=rs.getString(2); // group name
	
				capital=-c1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>Capital</td>
	<td></td>
	<td><%=capital%></td>
	
	<%
 		double fixedassets=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Fixed Assets'");
		   while(rs.next())
		   {
		        double fa1=rs.getDouble(1); // amount
		        String fa2=rs.getString(2); // group name
	
				fixedassets=fa1;
		   }
   		}
		catch(Exception e) {}
%>
	<td>Fixed Assets</td>
	<td></td>
	<td><%=fixedassets %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%	
		double securedloans=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Secured Loans'");
		   while(rs.next())
		   {
		        double sl1=rs.getDouble(1); // amount
		        String sl2=rs.getString(2); // group name
	
				securedloans=-sl1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>Secured loans</td>
	<td></td>
	<td><%=securedloans %></td>
	
<%	
		double investments=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Investments'");
		   while(rs.next())
		   {
		        double in1=rs.getDouble(1); // amount
		        String in2=rs.getString(2); // group name
	
				investments=-in1;	
		   }
   		}
		catch(Exception e) {}
%>
	
	<td>Investments</td>
	<td></td>
	<td><%=investments %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%	
		double unsecuredloans=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Unsecured Loans'");
		   while(rs.next())
		   {
		        double us1=rs.getDouble(1); // amount
		        String us2=rs.getString(2); // group name
	
				unsecuredloans=-us1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>Unsecured loans</td>
	<td></td>
	<td><%=unsecuredloans %></td>
	
	
	<th><font color=red>Current Assets:</font></th>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%	
		double closingstock=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Closing Stock'");
		   while(rs.next())
		   {
		        double cs1=rs.getDouble(1); // amount
		        String cs2=rs.getString(2); // group name
	
				closingstock=-cs1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td></td>
	<td></td>
	<td></td>
	<td>Closing Stock</td>
	<td><%=closingstock %></td>
	<td></td>
</tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%	
		double sundrydebtors=0.0,salestax=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT code, name, sum(amount), groupname FROM ( (SELECT ccode AS code, scname AS name, stota AS amount, sgroupname AS groupname FROM salesvoucher2) UNION ALL (SELECT ccode AS code, cdcname AS name, -(cdtotal) AS amount, cdgroupname AS groupname FROM CreditNote) UNION ALL (SELECT scode AS code, pvsname AS name, -(pvtota) AS amount, pgroupname AS groupname FROM purchasevoucher2) UNION ALL (SELECT scode AS code, dbsname AS name, dbtotal AS amount, dbgroupname AS groupname FROM DebitNote) UNION ALL (SELECT raccid AS code, raccname AS name, -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT racc2id AS code, raccname2 AS name, -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyaccid AS code, pyaccname AS name, pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyacc2id AS code, pyaccname2 AS name, pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jaccid AS code, jaccdeb AS name, jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT jacc2id AS code, jacccred AS name, -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Sundry Debtors' ");
		   while(rs.next())
		   {
			    String sdvalue1=rs.getString(1);
			    String sdvalue2=rs.getString(2);
		        sundrydebtors=rs.getDouble(3); // amount
		        String sdvalue4=rs.getString(4);
		   }
   		}
		catch(Exception e) {}
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT SUM(stamount) FROM SalesVoucher2");
		   while(rs.next())
		   {
		        salestax=rs.getDouble(1); // tax
		   }
   		}
		catch(Exception e) {}

%>

<%	
		double credit=0.0,credittax=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT SUM(cdtax) FROM CreditNote");
		   while(rs.next())
		   {
		        credittax=rs.getDouble(1);
		   }
   		}
		catch(Exception e) {}
%>

<% sundrydebtors= -(sundrydebtors); %>
<tr>
	<td></td>
	<td></td>
	<td></td>
	<td>Sundry Debtors</td>
	<td><%=sundrydebtors %></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%	
		double loansandadvances=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Loans And Advances'");
		   while(rs.next())
		   {
		        double la1=rs.getDouble(1); // amount
		        String la2=rs.getString(2); // group name
	
				loansandadvances=la1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<th><font color=red>Current Liabilities:</font></th>
	<td></td>
	<td></td>
	<td>Loans and Advances</td>
	<td><%=loansandadvances %></td>
	<td></td>
</tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%	
		double sundrycreditor=0.0,purchasetax=0.0;


		try
		{
		   st=con.createStatement();
  		   rs=st.executeQuery("SELECT code, name, sum(amount), groupname FROM ( (SELECT ccode AS code, scname AS name, stota AS amount, sgroupname AS groupname FROM salesvoucher2) UNION ALL (SELECT ccode AS code, cdcname AS name, -(cdtotal) AS amount, cdgroupname AS groupname FROM CreditNote) UNION ALL (SELECT scode AS code, pvsname AS name, -(pvtota) AS amount, pgroupname AS groupname FROM purchasevoucher2) UNION ALL (SELECT scode AS code, dbsname AS name, dbtotal AS amount, dbgroupname AS groupname FROM DebitNote) UNION ALL (SELECT raccid AS code, raccname AS name, -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT racc2id AS code, raccname2 AS name, -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyaccid AS code, pyaccname AS name, pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyacc2id AS code, pyaccname2 AS name, pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jaccid AS code, jaccdeb AS name, jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT jacc2id AS code, jacccred AS name, -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Sundry Creditors' ");
 		   while(rs.next())
   		   {
	   	 	 String sdvalue1=rs.getString(1);
	   		 String sdvalue2=rs.getString(2);
        	 sundrycreditor=rs.getDouble(3); // amount
       		 String sdvalue4=rs.getString(4);
  		   }
		}
		catch(Exception e) {}
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT SUM(pvtaxa) FROM PurchaseVoucher2");
		   while(rs.next())
		   {
		        purchasetax=rs.getDouble(1); //tax
		   }
   		}
		catch(Exception e) {}
%>

<%	
		double debit=0.0,debittax=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT SUM(dbtax) FROM DebitNote");
		   while(rs.next())
		   {
		        debittax=rs.getDouble(1);
		   }
   		}
		catch(Exception e) {}
%>

<% sundrycreditor= -(sundrycreditor); %>

<tr>
	<td>Sundry Creditors</td>
	<td><%=sundrycreditor %></td>
	<td></td>
	
	<%	
		double receiptsbank=0.0,rb1=0.0,rb2=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT SUM(ramount), SUM(ramount2) FROM ReceiptsVoucher2");
		   while(rs.next())
		   {
		        rb1=rs.getDouble(1); // amount
		        rb2=rs.getDouble(2);   
		   }
		   
		   receiptsbank=rb1+rb2;
   		}
		catch(Exception e) {}
	%>

	<%	
		double paymentbank=0.0,pyb1=0.0,pyb2=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT SUM(pyamount),SUM(pyamount2) FROM PaymentVoucher2");
		   while(rs.next())
		   {
		        double p1=rs.getDouble(1); // amount
		        double p2=rs.getDouble(1);
		        
		        pyb1=p1;
		        pyb1=p1;
		   }
		   paymentbank=pyb1+pyb2;
   		}
		catch(Exception e) {}
	%>
	
	<% double cashbank=receiptsbank-paymentbank; %>
	
	<td>Cash and Bank Balance</td>
	<td><%=cashbank %></td>
	
	<% double subtotal1=loansandadvances+sundrydebtors+closingstock+cashbank; %>
	
	<td><%=subtotal1 %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<% 		double dutiesandtaxes=0.0;
		dutiesandtaxes=-purchasetax+salestax+debittax-credittax; %>
		
<tr>
	<td>Duties and Taxes</td>
	<td><%=dutiesandtaxes %></td>
	<td></td>
	<td></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%	
		double provision=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Provision'");
		   while(rs.next())
		   {
		        double pr1=rs.getDouble(1); // amount
		        String pr2=rs.getString(2); // group name
	
				provision=pr1;	
		   }
   		}
		catch(Exception e) {}
%>


<tr>
	<td>Provision</td>
	<td><%=provision %></td>	
	
	<% double subtotal2=sundrycreditor+dutiesandtaxes+provision; %>
	
	<td><%=subtotal2 %></td>
	<td></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

		<% double profitandloss=0.0; %>
<tr>
	<td>Profit and Loss Account</td>
	<td></td>
	<td></td>
	<td></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

		<% double total1=subtotal2+capital+securedloans+unsecuredloans+profitandloss; %>
 
<tr>
	<th><font color=red>Total</font></th>
	<td></td>
	<td><%=total1 %></td>

		<% double total2=subtotal1+investments+fixedassets; %>
		
	<th><font color=red>Total</font></th>
	<td></td>
	<td><%=total2 %></td>
</tr>


</table>

</form>
</body>
</html>